<?php

/**
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `position` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
 */

class MyDB {
	private $_Con;
	const MYSQL_HOST = '198.23.51.222'; // Database Host
	const MYSQL_USER = 'technoor_papuji'; // Database User
	const MYSQL_PASS = 'papuji'; // Database Password
	const MYSQL_NAME = 'technoor_papuji'; // Database Name
	function __construct() {
		$this->_Con = mysql_connect ( self::MYSQL_HOST, self::MYSQL_USER, self::MYSQL_PASS );
		
		if (! $this->_Con) {
			exit ( 'Connection failed to MySQL host <b>' . self::MYSQL_HOST . '</b>' );
		}
		
		mysql_select_db ( self::MYSQL_NAME, $this->_Con );
		
		if (mysql_error ()) {
			exit ( 'Connection to <b>' . self::MYSQL_NAME . '</b> failed.' );
		}
	}
	
	/**
	 *
	 *
	 * selectFrom
	 *
	 * Simple method to select a result set from a mysql database
	 *
	 * @param str $table        	
	 * @param
	 *        	str / array $columns
	 * @param array $where        	
	 * @param boolean $like        	
	 * @param str $orderby        	
	 * @param str $direction        	
	 * @param int $limit        	
	 * @param int $offset        	
	 * @return array('sql' => $SQL, 'num' => $num, 'result' => $result);
	 *        
	 *        
	 *        
	 *        
	 *         Example Usage 1 :
	 *        
	 *         <code>
	 *         require 'MyDB.php';
	 *         $con = new MyDB();
	 *         $query = $con -> selectFrom("users", 
	 *         $columns = null, $where = null, 
	 *         $like = false, $orderby = "id", 
	 *         $direction = "DESC", 
	 *         $limit = null, $offset = null);
	 *         </code>
	 *        
	 *         Generated SQL:
	 *         SELECT * FROM users ORDER BY id DESC
	 *        
	 *         Return Example:
	 *        
	 *         Array
	 *         (
	 *         [sql] => SELECT * FROM users ORDER BY id DESC
	 *         [num] => 2
	 *         [result] => Array
	 *         (
	 *         [0] => Array
	 *         (
	 *         [id] => 2
	 *         [name] => Jane Bloggs
	 *         [position] => Wife
	 *         )
	 *        
	 *         [1] => Array
	 *         (
	 *         [id] => 1
	 *         [name] => Joe Bloggs
	 *         [position] => Owner
	 *         )
	 *        
	 *         )
	 *        
	 *         )
	 *        
	 *         Example Usage 2 :
	 *         <code>
	 *         require 'MyDB.php';
	 *         $con = new MyDB();
	 *         $query = $con->selectFrom($table = "people", $columns = array('id','name','position'), $where = array('name'=>'Joe Bloggs'), $like = false, $orderby = "id", $direction = "DESC", $limit = 2, $offset = 1);
	 *         </code>
	 *        
	 *         This would run the following SQL statement:
	 *         SELECT id, name, position FROM users WHERE name='Joe Bloggs' ORDER BY id DESC LIMIT 1, 2
	 *        
	 *        
	 *        
	 *         Example Usage 3 :
	 *        
	 *         <code>
	 *         require 'MyDB.php';
	 *         $con = new MyDB();
	 *         $query = $con->selectFrom($table = "users", $columns = array('id','name','position'), $where = array('name'=>'Joe'), $like = true, $orderby = "id", $direction = "DESC", $limit = 2, $offset = 1);
	 *         </code>
	 *        
	 *         This would run the following SQL statement:
	 *        
	 *         SELECT id, name, position FROM users WHERE name LIKE '%Joe%' ORDER BY id DESC LIMIT 1, 2
	 *        
	 *        
	 *        
	 *         Return Value:
	 *         array(
	 *         sql => The actual SQL statement that this file ran against the database,
	 *         num => The number of results fetched from the database,
	 *         result => each row set in its own array
	 *         )
	 *        
	 */
	public function selectFrom($table, $columns = null, $where = null, $like = false, $orderby = null, $direction = null, $limit = null, $offset = null) {
		$SQL = "SELECT ";
		if ($columns != null) {
			if (is_array ( $columns )) {
				$cols = implode ( ", ", $columns );
			} else {
				$cols = $columns;
			}
			$SQL .= $cols . " ";
		} else {
			$SQL .= "* ";
		}
		$SQL .= "FROM " . $table;
		
		if ($where != null) {
			$SQL .= " WHERE";
			if ($like == true) {
				$whe = $this->_helperWhereLikeGenerate ( $where );
			} else {
				$whe = $this->_helperWhereEqualsGenerate ( $where );
			}
			$SQL .= $whe;
		}
		if ($direction != null && $orderby != null) {
			if (strtolower ( $direction ) == "asc" || strtolower ( $direction ) == "desc") {
				$order = " ORDER BY " . $orderby . " " . $direction;
				$SQL .= $order;
			}
		}
		if ($limit != null) {
			if ($offset == null) {
				$lim = " LIMIT " . $limit;
				$SQL .= $lim;
			} else {
				$lim = " LIMIT " . $offset . ", " . $limit;
				$SQL .= $lim;
			}
		}
		
		echo $SQL;
		$exec = mysql_query ( $SQL, $this->_Con );
		
		$num = mysql_num_rows ( $exec );
		if ($num != 0) {
			$result = array ();
			while ( NULL != ($row = mysql_fetch_assoc ( $exec )) ) {
				$thisset = array ();
				foreach ( $row as $key => $val ) {
					$thisset [$key] = $val;
				}
				$result [] = $thisset;
			}
		}
		
		$returnarray = array (
				'sql' => $SQL,
				'num' => $num,
				'result' => $result 
		);
		
		return $returnarray;
	}
	
	/**
	 * insertInto
	 *
	 * Simple method to insert a row of data into a mysql
	 * database. This function relies on a mysql_insert_id()
	 * to be returned so you must have an auto increment
	 * field in the table.
	 *
	 * @param str $table        	
	 * @param array $fields
	 *        	Example Usage 1 :
	 *        	
	 *        	<code>
	 *        	require 'MyDB.php';
	 *        	$con = new MyDB();
	 *        	$query = $con -> insertInto("users", $fields = array("id" => "", "name" => "John Doe", "position" => "Unknown"));
	 *        	</code>
	 *        	
	 *        	Generated SQL:
	 *        	INSERT INTO users (id, name, position) VALUES ('', 'John Doe', 'Unknown')
	 *        	
	 *        	Note that we place id into the array put into the
	 *        	function but leave its value as empty. This is our
	 *        	auto-increment columns which we rely on to identify
	 *        	a successful insertion.
	 *        	
	 *        	Return Example: (Success)
	 *        	
	 *        	Array
	 *        	(
	 *        	[status] => success
	 *        	[id] => 4
	 *        	)
	 *        	
	 *        	
	 *        	Return Example: (Failure)
	 *        	
	 *        	Array
	 *        	(
	 *        	[status] => failure
	 *        	[id] => 0
	 *        	)
	 *        	
	 *        	
	 */
	public function insertInto($table = null, $fields) {
		$cleanfields = $this->_helperCleanFields ( $fields );
		
		$inserts = $this->_helperExtractFieldsValues ( $cleanfields );
		
		$SQL = "INSERT INTO ";
		$SQL .= $table . " ";
		$SQL .= $inserts ['fields'] . " ";
		$SQL .= "VALUES ";
		$SQL .= $inserts ['values'] . " ";
		
		mysql_query ( $SQL, $this->_Con );
		
		$id = mysql_insert_id ( $this->_Con );
		
		if (isset ( $id ) && is_numeric ( $id ) && $id != 0) {
			$status = "success";
		} else {
			$status = "failure";
			$id = 0;
		}
		
		$insertid = $id;
		
		$return = array (
				'status' => $status,
				'id' => $insertid 
		);
		
		return $return;
	}
	
	/**
	 * updateTable
	 *
	 * Simple method to update a MySQL table giving specific reference
	 * to values to set, and under what conditions (WHERE).
	 *
	 * Also supports %LIKE% when $like = true
	 *
	 * @param str $table        	
	 * @param array $fields        	
	 * @param array $where        	
	 * @param boolean $like
	 *        	Example Usage 1 :
	 *        	
	 *        	<code>
	 *        	require 'MyDB.php';
	 *        	$con = new MyDB();
	 *        	$query = $con -> updateTable("users", $fields = array("name" => "a","position" => "b"), $where = array("id"=>"2"), $like = true);
	 *        	</code>
	 *        	
	 *        	Generated SQL:
	 *        	UPDATE users SET name='a', position='b' WHERE id LIKE '%2%'
	 *        	
	 *        	Return Example: (Success)
	 *        	
	 *        	Array
	 *        	(
	 *        	[sql] => UPDATE users SET name='a', position='b' WHERE id LIKE '%2%'
	 *        	[status] => success
	 *        	[affected] => 1
	 *        	)
	 *        	
	 *        	
	 *        	Return Example: (Failure)
	 *        	
	 *        	Array
	 *        	(
	 *        	[sql] => UPDATE users SET nosuchfield='a', position='b' WHERE id LIKE '%2%'
	 *        	[status] => failure
	 *        	[affected] => 0
	 *        	)
	 *        	
	 *        	
	 */
	public function updateTable($table, $fields, $where = null, $like = false) {
		$cleanfields = $this->_helperCleanFields ( $fields );
		
		if ($where != null) {
			$wherebegin = " WHERE";
			if ($like == false) {
				$wherecommand = $this->_helperWhereEqualsGenerate ( $where );
			} else {
				$wherecommand = $this->_helperWhereLikeGenerate ( $where );
			}
			$wherecommand = $wherebegin . $wherecommand;
		} else {
			$wherecommand = "";
		}
		
		$updates = $this->_helperExtractUpdateValues ( $cleanfields );
		
		$SQL = "UPDATE ";
		$SQL .= $table . " ";
		$SQL .= $updates . "";
		$SQL .= $wherecommand . " ";
		
		mysql_query ( $SQL, $this->_Con );
		
		$num = mysql_affected_rows ( $this->_Con );
		
		if (isset ( $num ) && is_numeric ( $num ) && $num != 0 && $num != - 1) {
			$status = "success";
		} else {
			$status = "failure";
		}
		
		$affected = $num;
		
		$return = array (
				'sql' => $SQL,
				'status' => $status,
				'affected' => $affected 
		);
		
		return $return;
	}
	
	/**
	 * deleteFrom
	 *
	 * A simple method to delete data from a mysql database.
	 * This requires no real input other than a table name,
	 * HOWEVER... If you don't specify the $where array or the
	 * $limit then you will get some nasty surprises. I would
	 * strongly suggest you run this against a dummy database
	 * before testing on a production system.
	 *
	 * @param str $table        	
	 * @param array $where        	
	 * @param boolean $like        	
	 * @param int $limit
	 *        	Example Usage 1 : (Success)
	 *        	
	 *        	<code>
	 *        	require 'MyDB.php';
	 *        	$con = new MyDB();
	 *        	$query = $con -> deleteFrom("users", $where = array('name'=>'John Doe','position'=>'Unknown'), $like = false, $limit = 1);
	 *        	</code>
	 *        	
	 *        	Generated SQL:
	 *        	DELETE FROM users WHERE name='John Doe' AND position='Unknown' LIMIT 1
	 *        	
	 *        	Return Example: (Success)
	 *        	
	 *        	Array
	 *        	(
	 *        	[sql] => DELETE FROM users WHERE name='John Doe' AND position='Unknown' LIMIT 1
	 *        	[status] => success
	 *        	[affected] => 1
	 *        	)
	 *        	
	 *        	Example Usage 2 : (Success)
	 *        	
	 *        	<code>
	 *        	require 'MyDB.php';
	 *        	$con = new MyDB();
	 *        	$query = $con -> deleteFrom("users", $where = array('name'=>'John','position'=>'Unknown'), $like = true, $limit = 1);
	 *        	</code>
	 *        	
	 *        	Generated SQL:
	 *        	DELETE FROM users WHERE name LIKE '%John%' AND position LIKE '%Unknown%' LIMIT 1
	 *        	
	 *        	Return Example: (Success)
	 *        	
	 *        	Array
	 *        	(
	 *        	[sql] => DELETE FROM users WHERE name LIKE '%John%' AND position LIKE '%Unknown%' LIMIT 1
	 *        	[status] => success
	 *        	[affected] => 1
	 *        	)
	 *        	
	 *        	
	 *        	Example Usage 3 : (Failure)
	 *        	
	 *        	<code>
	 *        	require 'MyDB.php';
	 *        	$con = new MyDB();
	 *        	$query = $con -> deleteFrom("users", $where = array('nosuchfield'=>'John','position'=>'Unknown'), $like = false, $limit = 1);
	 *        	</code>
	 *        	
	 *        	Generated SQL:
	 *        	DELETE FROM users WHERE name LIKE '%John%' AND position LIKE '%Unknown%' LIMIT 1
	 *        	
	 *        	Return Example: (Failure)
	 *        	
	 *        	Array
	 *        	(
	 *        	[sql] => DELETE FROM users WHERE nosuchfield='John' AND position='Unknown' LIMIT 2
	 *        	[status] => failure
	 *        	[affected] => -1
	 *        	)
	 *        	
	 */
	public function deleteFrom($table, $where = null, $like = false, $limit = 1) {
		$SQL = "DELETE ";
		$SQL .= "FROM " . $table;
		
		if ($where != null) {
			$SQL .= " WHERE";
			if ($like == true) {
				$whe = $this->_helperWhereLikeGenerate ( $where );
			} else {
				$whe = $this->_helperWhereEqualsGenerate ( $where );
			}
			$SQL .= $whe;
		}
		
		if ($limit != null) {
			$lim = " LIMIT " . $limit;
			$SQL .= $lim;
		}
		
		mysql_query ( $SQL, $this->_Con );
		
		$num = mysql_affected_rows ( $this->_Con );
		
		if (isset ( $num ) && is_numeric ( $num ) && $num != 0 && $num != - 1) {
			$status = "success";
		} else {
			$status = "failure";
		}
		
		$affected = $num;
		
		$return = array (
				'sql' => $SQL,
				'status' => $status,
				'affected' => $affected 
		);
		
		return $return;
	}
	
	/**
	 * A helper function to generate x LIKE %y% AND y LIKE %z%
	 * 
	 * @param array $where        	
	 * @return str
	 */
	private function _helperWhereLikeGenerate($where) {
		if (! empty ( $where )) {
			$whe = "";
			$i = 0;
			foreach ( $where as $wKey => $wVal ) {
				$i ++;
				if ($i != 1) {
					$wheAdd = " AND ";
				} else {
					$wheAdd = " ";
				}
				$whe .= $wheAdd . $wKey . " LIKE '%" . mysql_real_escape_string ( $wVal, $this->_Con ) . "%'";
			}
		} else {
			$whe = "";
		}
		
		return $whe;
	}
	
	/**
	 * A helper function to generate x = y AND y = z
	 * 
	 * @param array $where        	
	 * @return str
	 */
	private function _helperWhereEqualsGenerate($where) {
		if (! empty ( $where )) {
			$whe = "";
			$i = 0;
			foreach ( $where as $wKey => $wVal ) {
				$i ++;
				if ($i != 1) {
					$wheAdd = " AND ";
				} else {
					$wheAdd = " ";
				}
				$whe .= $wheAdd . $wKey . "='" . mysql_real_escape_string ( $wVal, $this->_Con ) . "'";
			}
		} else {
			$whe = "";
		}
		
		return $whe;
	}
	
	/**
	 * A helper function to cleanse values
	 * 
	 * @param array $fields        	
	 * @return array clean key => values
	 */
	private function _helperCleanFields($fields) {
		$cleanfields = array ();
		
		foreach ( $fields as $fkey => $fval ) {
			
			$cleanfields [$fkey] = mysql_real_escape_string ( $fval, $this->_Con );
		}
		
		return $cleanfields;
	}
	
	/**
	 * A helper function to extract field names
	 * and values from an array of clean variables.
	 * 
	 * @param array $cleanfields        	
	 * @return array ( fields, values )
	 */
	private function _helperExtractFieldsValues($cleanfields) {
		$return = array ();
		
		if (! empty ( $cleanfields )) {
			$keys = array_keys ( $cleanfields );
			$vals = array_values ( $cleanfields );
			
			$insertFields = implode ( ", ", $keys );
			$insertFields = "(" . $insertFields . ")";
			$insertValues = implode ( "', '", $vals );
			$insertValues = "('" . $insertValues . "')";
			
			$return ['fields'] = $insertFields;
			$return ['values'] = $insertValues;
		}
		
		return $return;
	}
	
	/**
	 * A helper function to extract the cleansed fields
	 * into a string for the UPDATE command
	 * 
	 * @param array $cleanfields        	
	 */
	private function _helperExtractUpdateValues($cleanfields) {
		$return = "";
		
		if (! empty ( $cleanfields )) {
			$return .= "SET";
			$i = 0;
			foreach ( $cleanfields as $key => $value ) {
				$i ++;
				if ($i != 1) {
					$begin = ",";
				} else {
					$begin = "";
				}
				$return .= $begin . " " . $key . "='" . $value . "'";
			}
		}
		
		return $return;
	}
}

?>